package com.chinatelecom.ifree.business.dao.order.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import com.chinatelecom.ifree.business.dao.base.DbcpPool;
import com.chinatelecom.ifree.business.dao.order.OrderDao;
import com.chinatelecom.ifree.business.domain.order.FcodeEntity;
import com.chinatelecom.ifree.business.domain.order.FcodeStatus;
import com.chinatelecom.ifree.business.domain.order.OrderEntity;

@Repository
public class OrderDaoImpl extends BaseDaoSupport<OrderEntity> implements OrderDao {
	private static transient Logger logger = LoggerFactory.getLogger(OrderDaoImpl.class);
	private static SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	@Override
	public OrderEntity getOrderByMobile(String mobile) throws Exception {
		if (logger.isInfoEnabled())
			logger.info("getOrderByMobile=====mobile={}", mobile);
		sql = "SELECT * FROM order_info WHERE mobile=?";
		return super.getEntityByPK(mobile);
	}

	@Override
	public void saveOrder(OrderEntity order) throws Exception {
		if (logger.isInfoEnabled())
			logger.info("saveOrder=====order={}", order);
		sql = "INSERT INTO order_info(MOBILE,ORDER_NAME,F_CODE,ORDER_TIME) VALUES(?,?,?,?)";
		super.saveEntity(order);
	}

	@Override
	public void executeSql(PreparedStatement statement, OrderEntity order) throws SQLException {
		statement.setString(1, order.getMobile());
		statement.setString(2, order.getName());
		statement.setString(3, order.getFcode());
		statement.setTimestamp(4, order.getOrderTime());
	}

	@Override
	public OrderEntity executeSingleResultSet(ResultSet rs) throws Exception {
		OrderEntity entity = null;
		if (rs.next()) {
			entity = new OrderEntity();
			entity.setMobile(rs.getString("MOBILE"));
			entity.setName(rs.getString("ORDER_NAME"));
			entity.setFcode(rs.getString("F_CODE"));
			entity.setOrderTime(rs.getTimestamp("ORDER_TIME"));
		}
		return entity;
	}

	@Override
	public void executeBatchSql(PreparedStatement state, OrderEntity[] t) throws Exception {

	}

	@Override
	public OrderEntity saveOrderInfo(OrderEntity order) throws Exception {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			// 取出F码
			String sql1 = "SELECT * FROM fcode_info WHERE F_STATUS=0 for update";
			conn = DbcpPool.getInstance().getConn();
			conn.setAutoCommit(false);
			statement = conn.prepareStatement(sql1);
			statement.setFetchSize(1);// 取一条
			rs = statement.executeQuery();
			FcodeEntity entity = null;
			if (rs.next()) {
				entity = new FcodeEntity();
				entity.setFcode(rs.getString("F_CODE"));
				entity.setStatus(FcodeStatus.getStatus(rs.getInt("F_STATUS")));
				entity.setCreateTimes(rs.getTimestamp("CREATE_TIME"));
				entity.setUpdateTimes(rs.getTimestamp("UPDATE_TIME"));
			}
			rs.close();
			statement.close();
			if (entity != null) {
				// 更新F码状态
				String sql2 = "UPDATE fcode_info SET F_STATUS=1,UPDATE_TIME=? WHERE F_CODE=?";
				statement = conn.prepareStatement(sql2);
				statement.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
				statement.setString(2, entity.getFcode());
				statement.executeUpdate();
				statement.close();
				// 保存预约信息
				order.setOrderTime(new Timestamp(System.currentTimeMillis()));
				order.setFcode(entity.getFcode());
				String sql3 = "INSERT INTO order_info(MOBILE,ORDER_NAME,F_CODE,ORDER_TIME) VALUES(?,?,?,?)";
				statement = conn.prepareStatement(sql3);
				statement.setString(1, order.getMobile());
				statement.setString(2, order.getName());
				statement.setString(3, order.getFcode());
				statement.setTimestamp(4, order.getOrderTime());
				statement.execute();
			}
			conn.commit();
			if (entity != null)
				return order;
			else
				return null;
		} catch (Exception e) {
			logger.error("saveOrderInfo", e);
			throw e;
		} finally {
			DbcpPool.getInstance().closeConnection(statement, conn);
		}
	}

	@Override
	public long getTotalOrder() throws Exception {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT COUNT(*) as total_order FROM order_info";
			conn = DbcpPool.getInstance().getConn();
			statement = conn.prepareStatement(sql);
			rs = statement.executeQuery();
			if (rs.next()) {
				return rs.getLong("total_order");
			} else {
				return 0;
			}
		} catch (Exception e) {
			logger.error("getTotalOrder", e);
			throw e;
		} finally {
			DbcpPool.getInstance().closeConnection(rs, statement, conn);
		}
	}

	@Override
	public List<OrderEntity> getOrderInfo(Date fromDate, Date toDate, int start, int end) throws Exception {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		List<OrderEntity> list = null;
		try {
			long total = getTotalOrder(fromDate, toDate);
			if (start > total)
				start = new Long(total).intValue();
			if (end > total)
				end = new Long(total).intValue();
			list = new ArrayList<OrderEntity>();
			String sql = "SELECT * FROM order_info WHERE order_time between STR_TO_DATE(?,'%Y-%m-%d %H:%i:%s') and STR_TO_DATE(?,'%Y-%m-%d %H:%i:%s') ORDER BY order_time asc limit ?,?";
			conn = DbcpPool.getInstance().getConn();
			statement = conn.prepareStatement(sql);
			statement.setString(1, dateFormat.format(fromDate));
			statement.setString(2, dateFormat.format(toDate));
			statement.setInt(3, start);
			statement.setInt(4, end);
			rs = statement.executeQuery();
			while (rs.next()) {
				OrderEntity entity = new OrderEntity();
				entity = new OrderEntity();
				entity.setMobile(rs.getString("MOBILE"));
				entity.setName(rs.getString("ORDER_NAME"));
				entity.setFcode(rs.getString("F_CODE"));
				entity.setOrderTime(rs.getTimestamp("ORDER_TIME"));
				list.add(entity);
			}
		} catch (Exception e) {
			logger.error("getOrderInfo", e);
			throw e;
		} finally {
			DbcpPool.getInstance().closeConnection(rs, statement, conn);
		}
		return list;
	}

	@Override
	public long getTotalOrder(Date fromDate, Date toDate) throws Exception {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT count(*) as total_num FROM order_info WHERE order_time between STR_TO_DATE(?,'%Y-%m-%d %H:%i:%s') and STR_TO_DATE(?,'%Y-%m-%d %H:%i:%s') ORDER BY order_time asc";
			conn = DbcpPool.getInstance().getConn();
			statement = conn.prepareStatement(sql);
			statement.setString(1, dateFormat.format(fromDate));
			statement.setString(2, dateFormat.format(toDate));
			rs = statement.executeQuery();
			if (rs.next()) {
				return rs.getLong("total_num");
			} else {
				return 0;
			}
		} catch (Exception e) {
			logger.error("getOrderInfo", e);
			throw e;
		} finally {
			DbcpPool.getInstance().closeConnection(rs, statement, conn);
		}
	}
}
